package com.dragon.jdbc;

import java.sql.*;
import java.util.Arrays;
import java.util.stream.IntStream;

/**
 * 批量操作示例
 *
 * @author dragon
 * @since 2019/9/25 11:11
 */
public class BatchInsertDemo {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        long start = System.currentTimeMillis();
        Connection connection = getConnection();
        long end = System.currentTimeMillis();
        System.out.println("cost time:" + (end - start) + "mills");
        // speed time:55105mills
//        batchInsertWithStatement(connection);
        // speed time:54185mills
       batchInsertWithPreparedStatement(connection);
//        insertTest(connection);
    }

    public static void insertTest(Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("update test set name = ? where id = ?");
        preparedStatement.setString(1, "dragbonsdgf");
        preparedStatement.setObject(2, "");
        int i = preparedStatement.executeUpdate();
        System.out.println("影响条数:" + i);
        releaseResource(connection, preparedStatement, null);
    }

    public static void batchInsertWithStatement(Connection connection) throws SQLException {
        Long start = System.currentTimeMillis();
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement();
        IntStream.range(0, 2)
                .forEach(i -> {
                    try {
                        String sql = "insert into test(name) values("+"'name" + i + "')";
                        statement.addBatch(sql);
                    } catch (SQLException e) {
                       throw new RuntimeException(e);
                    }
                });
        int[] ints = statement.executeBatch();

        connection.commit();
        Long end = System.currentTimeMillis();
        System.out.println("speed time:" + (end - start) + "mills");
        releaseResource(connection, statement, null);
    }

    public static void batchInsertWithPreparedStatement(Connection connection) throws SQLException {
        Long start = System.currentTimeMillis();
        connection.setAutoCommit(false);
        String sql = "insert into test(name) values(?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        IntStream.range(0, 20000)
                .forEach(i -> {
                    try {
                        statement.setString(1, "name" + i);
                        statement.addBatch();
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                });
        int[] ints = statement.executeBatch();
        connection.commit();
        System.out.println(ints.length);
        Long end = System.currentTimeMillis();
        System.out.println("speed time:" + (end - start) + "mills");
        releaseResource(connection, statement, null);
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        // 定义连接的 url
        String url = "jdbc:mysql://localhost/mybatis_test?allowMultiQueries=true";
        // 通过DriverManager 获得注册的驱动，原理是通过Driver.accepts(url) 方法判断来判断，来获取驱动
        Driver driver = DriverManager.getDriver(url);
        // 通过DriverManager 获取连接对象
        Connection connection = DriverManager.getConnection(url, "root", "dragon4369");

        return connection;
    }

    public static void releaseResource(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }
    }
}
